package com.yishu.Dao;

import com.mysql.cj.api.mysqla.result.Resultset;
import com.yishu.entity.User;
import com.yishu.util.DBConnection;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

    DBConnection dbConnection = new DBConnection();

    /**
     * 登录验证
     *
     * @param connection
     * @param user
     * @return
     * @throws Exception
     */
    public User login(Connection connection, User user) throws Exception {
        connection = dbConnection.getCon();

        User resultUser = null;//查询结果User
        String sql = "select * from t_users where userName= '" + user.getUserName() + "' and password='" + user.getPassword() + "'";
        Statement statement = connection.createStatement();
        //执行
        ResultSet resultSet = statement.executeQuery(sql);

        if (resultSet.next()) {
            resultUser = new User();
            resultUser.setUserId(resultSet.getInt("userId"));
            resultUser.setUserName(resultSet.getString("userName"));
            resultUser.setEmail(resultSet.getString("email"));
            resultUser.setPassword(resultSet.getString("password"));
            resultUser.setStatus(resultSet.getInt("status"));
        }


        return resultUser;
    }

    /**
     * 用户注册
     *
     * @param connection
     * @param user
     * @return
     * @throws Exception
     */
    public int userRegister(Connection connection, User user) throws Exception {
        connection = dbConnection.getCon();

        String sql = "insert into t_users(userName,password) values ('" + user.getUserName() + "','" + user.getPassword() + "')";
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(sql);


        return result;
    }

    /**
     * 查询用户是否冻结
     * @param connection
     * @param user
     * @return
     * @throws Exception
     */
    public int showStatus(Connection connection, User user) throws Exception {
        connection = dbConnection.getCon();
        String sql = "select status from t_users where userName='" + user.getUserName() + "' and password='" + user.getPassword() + "'";
        int status = 0;
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        if (resultSet.next()) {
            status = resultSet.getInt("status");
        }


        return status;
    }

    /**
     * 获取用户列表
     * @param connection
     * @return
     * @throws Exception
     */
    public List<User> showUserList(Connection connection) throws Exception {
        connection = dbConnection.getCon();

        List<User> userList = new ArrayList<>();
        String sql = "select * from t_users";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            User user = new User();
            user.setUserId(resultSet.getInt("userId"));
            user.setUserName(resultSet.getString("userName"));
            user.setPassword(resultSet.getString("password"));
            user.setStatus(resultSet.getInt("status"));
            user.setEmail(resultSet.getString("email"));
            userList.add(user);
        }

        return userList;
    }

    /**
     * 更新用户状态
     * @param connection
     * @param userId
     * @param status
     * @return
     * @throws Exception
     */
    public int updateStatus(Connection connection,int userId,int status) throws Exception {
        connection = dbConnection.getCon();

        String sql = "update t_users set status='"+status+"'where userId = '"+userId+"'";
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(sql);

        return result;
    }

    /**
     * 查询一个用户
     * @param userId
     * @param connection
     * @return
     * @throws Exception
     */
    public User showAUser(int userId,Connection connection)throws Exception {
        connection = dbConnection.getCon();

        String sql = "select * from t_users where userId='"+userId+"'";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        User user = new User();
        if(resultSet.next()) {
            user.setUserId(resultSet.getInt("userId"));
            user.setUserName(resultSet.getString("userName"));
            user.setPassword(resultSet.getString("password"));
            user.setEmail(resultSet.getString("email"));
        }

        return user;
    }

    /**
     * 更新一个用户
     * @param userId
     * @param userName
     * @param password
     * @param email
     * @param connection
     * @return
     * @throws Exception
     */
    public int updateUser(int userId,String userName,String password,String email,Connection connection) throws Exception{
        connection = dbConnection.getCon();

        String sql = "update t_users set userName='"+userName+"',password='"+password+"',email='"+email+"' where userId = '"+userId+"'";
        Statement statement = connection.createStatement();
        int result= statement.executeUpdate(sql);

        return result;
    }

    /**
     * 删除一个用户信息
     * @param userId
     * @param connection
     * @return
     * @throws Exception
     */
    public int deleteUser(int userId,Connection connection) throws Exception{
        connection = dbConnection.getCon();

        String sql = "delete FROM t_users where userId='"+userId+"'";
        Statement statement = connection.createStatement();
        int result = statement.executeUpdate(sql);

        return  result;
    }
}
